IF OBJECT_ID('ssf.job_exec_get_definition') IS NOT NULL
	DROP FUNCTION ssf.job_exec_get_definition
GO

CREATE FUNCTION ssf.job_exec_get_definition (
	@job_name NVARCHAR(128)
)
RETURNS XML
AS
BEGIN
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

DECLARE @job_definition XML
;

;
WITH
ParentJob AS (
	SELECT @job_name AS ParentJob
		, step_id
		, subsystem
		, on_success_action
		, on_success_step_id
		, on_fail_action
		, on_fail_step_id
		, retry_attempts
		, retry_interval
		, step_name
		, ssf.job_exec_parse_jobstep_command_job_name(command) AS step_job_name
	FROM ssf.job_get_step_info(ssf.job_get_job_id(@job_name))
)
SELECT @job_definition = (
	SELECT @job_name AS "@JobName"
		, (
			SELECT p1.step_id AS "@StepID"
				, p1.on_success_action AS "@OnSuccessAction"
				, p1.on_success_step_id AS "@OnSuccessStepID"
				, p1.on_fail_action AS "@OnFailAction"
				, p1.on_fail_step_id AS "@OnFailStepID"
				, p1.retry_attempts AS "@RetryAttempts"
				, p1.retry_interval AS "@RetryInterval"
				, p1.subsystem AS "Subsystem"
				, p1.step_name AS "StepName"
				, (
					SELECT p2.step_job_name AS "@JobName"
						, (
							SELECT cs.step_id AS "@StepID"
								, cs.on_success_action AS "@OnSuccessAction"
								, cs.on_success_step_id AS "@OnSuccessStepID"
								, cs.on_fail_action AS "@OnFailAction"
								, cs.on_fail_step_id AS "@OnFailStepID"
								, cs.retry_attempts AS "@RetryAttempts"
								, cs.retry_interval AS "@RetryInterval"
								, cs.subsystem AS "Subsystem"
								, cs.step_name AS "StepName"
							FROM ssf.job_get_step_info(ssf.job_get_job_id(p2.step_job_name)) cs
							ORDER BY cs.step_id
							FOR XML PATH(N'JobStep'), TYPE
						)
					FROM ParentJob p2
					WHERE p2.step_id = p1.step_id
					FOR XML PATH(N'JobDefinition'), TYPE
				)
			FROM ParentJob p1
			ORDER BY p1.step_id
			FOR XML PATH(N'JobStep'), TYPE
		)
	FOR XML PATH(N'JobDefinition'), TYPE
)
;

RETURN @job_definition
;

END
GO
